﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using PMQLCHH.DTO;

namespace PMQLCHH.DAO
{
    class GoiHangDAO
    {
        public static DataTable LoadDSPhieuGoi()
        {
            string sql = "select MaPhieuGoiHang,TenNhaCungCap,SoDienThoai,PhieuGoiHang.NgayLap as NgayLap, TongSoLuongTra, CASE PhieuGoiHang.TrangThai WHEN 0 THEN N'Chưa Duyệt' ELSE N'Đã Duyệt' END AS 'TrangThai' from PhieuGoiHang, NhaCungCap where PhieuGoiHang.MaNhaCungCap=NhaCungCap.MaNhaCungCap order by NgayLap DESC";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayNgayLapPTCuNhat()
        {
            string strQuery = "";
            strQuery = "SELECT TOP 1 NgayLap FROM PhieuGoiHang ORDER BY NgayLap ASC";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }
        public static DataTable LocPhieuTra(string MaPT, string strTuNgay, string strDenNgay, int TrangThai)
        {
            string strQuery = "";
            strQuery = String.Format("SELECT MaPhieuGoiHang,TenNhaCungCap,SoDienThoai,PhieuGoiHang.NgayLap as NgayLap, TongSoLuongTra, CASE PhieuGoiHang.TrangThai WHEN 0 THEN N'Chưa Duyệt' ELSE N'Đã Duyệt' END AS 'TrangThai' from PhieuGoiHang, NhaCungCap where PhieuGoiHang.MaNhaCungCap=NhaCungCap.MaNhaCungCap AND MaPhieuGoiHang LIKE  '%{0}%' AND PhieuGoiHang.NgayLap BETWEEN '{1}' AND '{2} 23:59:59'  ", MaPT, strTuNgay, strDenNgay);
            if (TrangThai != 2)//trang thai la chon tat ca
            {
                strQuery += String.Format("AND PhieuGoiHang.TrangThai = {0}", TrangThai);
            }
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }
        public static bool XoaTatCaTrongCTPT()
        {
            string sql = "delete from ChiTietPhieuGoiHang where TrangThai=0";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool XoaTatCaTrongPT()
        {
            string sql = "delete from PhieuGoiHang where TrangThai=0";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool XoaCTPhieuTraTheoMaPT(PhieuGoiHangDTO pg)
        {
            string sql = "delete from ChiTietPhieuGoiHang where MaPhieuGoiHang='" + pg.MaPhieuGoi + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool XoaPhieuGoiTheoMaPT(PhieuGoiHangDTO pg)
        {
            string sql = "delete from PhieuGoiHang where MaPhieuGoiHang='" + pg.MaPhieuGoi + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static DataTable LoadCTPTTheoMPT(PhieuGoiHangDTO pg)
        {
            string sql = "select ChiTietPhieuGoiHang.MaMatHang as MaMatHang,MatHang.TenMatHang as TenMatHang,LoaiMatHang.TenLoaiMatHang as LoaiMatHang,DonViTinh.TenDonVi as DonViTinh, SoLuongTra";
            sql += " from MatHang,ChiTietPhieuGoiHang,LoaiMatHang,DonViTinh";
            sql += " where ChiTietPhieuGoiHang.MaMatHang=MatHang.MaMatHang and MatHang.LoaiMatHang=LoaiMatHang.MaLoaiMatHang and MatHang.DonViTinh=DonViTinh.MaDonVi and MaPhieuGoiHang='" + pg.MaPhieuGoi + "'";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }
        public static bool CapNhatTrangThaiPhieuTra(PhieuGoiHangDTO pg)
        {
            string sql = "update PhieuGoiHang set TrangThai=1 where MaPhieuGoiHang='" + pg.MaPhieuGoi + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool CapNhatTrangThaiCTPhieuTra(PhieuGoiHangDTO pg)
        {
            string sql = "update ChiTietPhieuGoiHang set TrangThai=1 where MaPhieuGoiHang='" + pg.MaPhieuGoi + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool UpDateTableMatHang(ChiTietPhieuGoiHangDTO ctpg)
        {
            //Đã sửa lại-Lâm Chương Trường Thịnh
            string sql = "update MatHang set SoLuongTon=SoLuongTon -" + ctpg.SoLuong + " where MaMatHang='" + ctpg.MaMatHang + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static DataTable GetMaGoiHangLonNhat()
        {
            string sql = "Select top 1 MaPhieuGoiHang from PhieuGoiHang order by MaPhieuGoiHang desc";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }
        public static bool InSertTablePhieuTra(PhieuGoiHangDTO pg)
        {
            string sql = String.Format("insert into PhieuGoiHang(MaPhieuGoiHang,MaNhaCungCap,TongSoLuongTra,TrangThai) values(N'{0}',N'{1}',{2},{3})", pg.MaPhieuGoi, pg.MaNhaCungCap, pg.TongSoLuongTra,  pg.TrangThai);
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool InSertTableChiTietPhieuGoi(ChiTietPhieuGoiHangDTO ctpg)
        {
            string sql = String.Format("insert into ChiTietPhieuGoiHang(MaPhieuGoiHang,MaMatHang,SoLuongTra,TrangThai) values(N'{0}',N'{1}',{2},{3})", ctpg.MaPhieuGoi, ctpg.MaMatHang, ctpg.SoLuong, ctpg.TrangThai);
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static DataTable LaySoLuongTonTheoMaMatHang(MatHangDTO MH)
        {
            string sql = "select SoLuongTon from MatHang where MaMatHang='" + MH.MaMatHang + "'";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

    }
}
